using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;


namespace Lib.Utilities
{
    public class DatabaseUtility
    {
        SqlConnection DBCon;

        //SqlConnection SqlCon = new SqlConnection();
        public DatabaseUtility(string connectionString)
        {
            DBCon = new SqlConnection();                                                                   DBCon.ConnectionString =connectionString; 
        }

        void OpenDB()
        {
            if (DBCon.State != ConnectionState.Open)
                DBCon.Open();
        }

        void CloseDB()
        {
            if (DBCon.State != ConnectionState.Closed)
                DBCon.Close();
        }

        /// <summary>
        /// Get list of database table
        /// </summary>
        /// <returns>String array which contain all name of tables in database</returns>
        public string[] GetTableList()
        {
            DataTable DT = GetTableList(true);
            string[] TableList = new string[DT.Rows.Count];
            for (int i = 0; i < TableList.Length; i++)
                TableList[i] = (string)DT.Rows[i][2];
            return TableList;
            //foreach (DataRow DR in DT.Rows)
            //    comboBox1.Items.Add(DR[2]);
            //foreach (DataColumn dc in SqlCon.GetSchema("Tables", new string[] { null, null, null, "BASE TABLE" }).Columns)
        }

        /// <summary>
        /// Get table list of the database
        /// </summary>
        /// <param name="asDataTable">Optional value, no affect to result</param>
        /// <returns>Datatable which contain all infomations of tables in database</returns>
        public DataTable GetTableList(bool asDataTable)
        {
            OpenDB();
            DataTable DT = DBCon.GetSchema("Tables", new string[] { null, null, null, "BASE TABLE" });
            CloseDB();
            return DT;
        }
        /// <summary>
        /// Get COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM from table "Table"
        /// </summary>
        /// <param name="Table">Table to get Column Info</param>
        /// <returns>Datatable which contain column's informations: COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM values</returns>
        public DataTable getColumnList(string Table)
        {
            //SqlCommand scmd = new SqlCommand("SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = '" + Table + "' ORDER BY ORDINAL_POSITION ASC", DBCon);
            SqlCommand scmd = new SqlCommand("SELECT Column_Name FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = '" + Table + "' ORDER BY ORDINAL_POSITION ASC", DBCon);
            SqlDataAdapter ds = new SqlDataAdapter(scmd);
            DataTable DT = new DataTable();
            ds.Fill(DT);
            return (DT);
        }

        public string InsertData(string Table, string FieldList, object[] ValueList)
        {
            if (string.IsNullOrEmpty(FieldList) || ValueList.Length <= 0)
                return "Null data inserting! Check your algorithm or values!";
            if (FieldList.IndexOf(',') >= 0 && ValueList.Length > 1)
                if (FieldList.Split(new char[] { ',' }).Length != ValueList.Length)
                    return "Number of inserted field and data doesn't match! Check your algorithm again!";
            string parameterList ="";
            for(int i =0; i<ValueList.Length; i++)
                parameterList+=", @params"+i.ToString();
            if(!string.IsNullOrEmpty(parameterList))
                parameterList = parameterList.Remove(0, 1);
            string SQL = "INSERT INTO " + Table + "("+FieldList+") VALUES ("+parameterList+")";
            SqlCommand scmd = new SqlCommand(SQL, DBCon);
            for (int i = 0; i < ValueList.Length; i++)
                if(ValueList[i]==null)
                    scmd.Parameters.Add(new SqlParameter("@params" + i.ToString(), DBNull.Value));
                else
                    scmd.Parameters.Add(new SqlParameter("@params" + i.ToString(), ValueList[i]));
            OpenDB();
            scmd.ExecuteNonQuery();
            CloseDB();
            return "Success!";
        }

        public DataTable SelectData(string Table)
        {
            return SelectData(Table, null);
        }
        
        public DataTable SelectData(string Table, string Where)
        {
            if (string.IsNullOrEmpty(Table))
                return null;
            string SQL = "SELECT * FROM " + Table + (string.IsNullOrEmpty(Where) ? "" : " WHERE " + Where);
            SqlCommand scmd = new SqlCommand(SQL, DBCon);
            SqlDataAdapter ds = new SqlDataAdapter(scmd);
            DataTable DT = new DataTable();
            ds.Fill(DT);
            return (DT);
        }

        public DataTable SelectData(string Table, string Where, int startIndex, int length, ref int totalRows)
        {
            if (string.IsNullOrEmpty(Table))
                return null;
            string SQL = "SELECT * FROM " + Table + (string.IsNullOrEmpty(Where) ? "" : " WHERE " + Where);
            SqlCommand scmd = new SqlCommand(SQL, DBCon);
            SqlDataAdapter ds = new SqlDataAdapter(scmd);
            DataTable DT = new DataTable();
            ds.Fill(DT);
            if(DT!=null)
                totalRows = DT.Rows.Count;
            DT = null;
            DT = new DataTable();
            ds.Fill(startIndex, length, new DataTable[] { DT });
            return (DT);
        }

        public string getAutoCompleteData(string Table, string Field)
        {
            if (string.IsNullOrEmpty(Table) || string.IsNullOrEmpty(Field))
                return null;

            string SQL = "SELECT DISTINCT " + Field + " FROM " + Table;
            SqlCommand scmd = new SqlCommand(SQL, DBCon);
            SqlDataAdapter ds = new SqlDataAdapter(scmd);
            DataTable DT = new DataTable();
            ds.Fill(DT);

            if (DT != null && DT.Rows.Count > 0)
            {
                string dl="";
                foreach (DataRow dr in DT.Rows)
                {
                    dl += ",\"" + dr[Field].ToString() + "\"";
                }
                if (dl.StartsWith(","))
                    dl = dl.Remove(0, 1);
                return dl;
            }
            else
                return null;
        }
    }
}
